"""数据集导入相关路由"""

import json
import logging
import os
import tempfile
import uuid
from datetime import datetime
from typing import List

import numpy as np
import pandas as pd
from fastapi import APIRouter, File, HTTPException, UploadFile

from backend.common.config import config_manager
from backend.database.db import db_manager

logger = logging.getLogger("api.datasets")

router = APIRouter(
    prefix=f"/{config_manager.api.version}/datasets",
    tags=["Datasets"],
    responses={404: {"description": "Not found"}},
)

SUPPORTED_EXTENSIONS = {".xlsx", ".xls", ".csv"}


def _detect_extension(filename: str) -> str:
    if not filename:
        return ""
    ext = os.path.splitext(filename)[1].lower()
    return ext


def _normalize_value(value) -> str:
    if pd.isna(value):
        return ""
    if isinstance(value, (np.generic,)):
        try:
            return str(value.item())
        except Exception:
            return str(value)
    if isinstance(value, (int, float)):
        return str(value)
    if isinstance(value, bytes):
        return value.decode("utf-8", errors="ignore")
    return str(value)


def _row_to_message(row) -> str:
    record = {}
    for column in row.index:
        value = row[column]
        normalized = _normalize_value(value)
        if normalized:
            record[column] = normalized
    if not record:
        return "导入的 Excel 行为空"
    return json.dumps(record, ensure_ascii=False)


@router.post("/import/excel")
async def import_excel_dataset(file: UploadFile = File(...)):
    """
    导入 Excel/CSV 文件并批量创建会话
    """
    ext = _detect_extension(file.filename or "")
    if ext not in SUPPORTED_EXTENSIONS:
        raise HTTPException(status_code=400, detail="仅支持上传 xls/xlsx/csv 文件")

    tmp_path = None
    try:
        contents = await file.read()
        with tempfile.NamedTemporaryFile(delete=False, suffix=ext) as tmp_file:
            tmp_file.write(contents)
            tmp_path = tmp_file.name

        if ext in {".xlsx", ".xls"}:
            df = pd.read_excel(tmp_path)
        else:
            df = pd.read_csv(tmp_path)
    except Exception as exc:
        logger.error(f"解析Excel失败: {exc}")
        raise HTTPException(status_code=400, detail=f"解析Excel失败: {exc}")
    finally:
        if tmp_path and os.path.exists(tmp_path):
            try:
                os.unlink(tmp_path)
            except OSError:
                pass

    if df.empty:
        return {
            "success": True,
            "importedCount": 0,
            "sessions": [],
        }

    preferred_title_columns = ["title", "Title", "name", "名称", "主题"]
    preferred_content_columns = ["content", "Content", "message", "消息", "描述", "detail", "Details"]
    imported_sessions: List[dict] = []

    current_time = datetime.now()
    with db_manager.get_cursor() as cursor:
        for index, row in df.iterrows():
            session_id = str(uuid.uuid4())

            title = None
            for column in preferred_title_columns:
                if column in df.columns:
                    cell_value = row.get(column)
                    normalized = _normalize_value(cell_value)
                    if normalized:
                        title = normalized.strip()
                        break
            if not title:
                title = f"导入会话 {index + 1}"

            message_content = None
            for column in preferred_content_columns:
                if column in df.columns:
                    value = row.get(column)
                    normalized = _normalize_value(value)
                    if normalized:
                        message_content = normalized
                        break
            if not message_content:
                message_content = _row_to_message(row)

            if db_manager.db_type == "sqlite":
                cursor.execute(
                    """
                    INSERT INTO sessions (id, title, created_at, updated_at)
                    VALUES (?, ?, ?, ?)
                    """,
                    (session_id, title, current_time, current_time),
                )
            else:
                cursor.execute(
                    """
                    INSERT INTO sessions (id, title, created_at, updated_at)
                    VALUES (%s, %s, %s, %s)
                    """,
                    (session_id, title, current_time, current_time),
                )

            db_manager.insert_session_message(
                session_id=session_id,
                role="assistant",
                content=message_content,
                metadata={
                    "source": "excel_import",
                    "row_index": index,
                    "filename": file.filename or "unknown",
                },
            )

            imported_sessions.append(
                {
                    "id": session_id,
                    "name": title,
                    "startTime": current_time.isoformat(),
                    "lastMessageTime": current_time.isoformat(),
                    "messageCount": 1,
                }
            )

    logger.info(f"Excel 导入完成，成功创建 {len(imported_sessions)} 个会话")
    return {
        "success": True,
        "importedCount": len(imported_sessions),
        "sessions": imported_sessions,
    }
